package com.moarea.app.util;

import org.apache.commons.fileupload.FileItem;
import org.apache.commons.fileupload.FileItemFactory;
import org.apache.commons.fileupload.FileUploadException;
import org.apache.commons.fileupload.disk.DiskFileItemFactory;
import org.apache.commons.fileupload.servlet.ServletFileUpload;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import javax.faces.application.Application;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.File;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.text.DateFormat;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.*;


public class ExcelUtil extends FileExport{
	public static void deleteFolder(String dir) {
		  File delfolder=new File(dir); 
		  File oldFile[] = delfolder.listFiles();
		  try 
		  { 
		     for (int i = 0; i < oldFile.length; i++)
		     {
		        oldFile[i].delete();
		     }
		  } 
		  catch (Exception e) 
		  { 
		  }
	}
    public static String getRootPath() {
        String result = Application.class.getResource("Application.class").toString().replaceAll("%20", " ");
        int index = result.indexOf("WEB-INF");
        if (index == -1) {
                index = result.indexOf("bin");
        }
        result = result.substring(0, index);
        if (result.startsWith("jar")) {
                result = result.substring(10);
        } else if (result.startsWith("file")) {
                result = result.substring(6);
        }
        if (result.endsWith("/"))
                result = result.substring(0, result.length() - 1);
        return result;
}
	public List<String[]> getDataFromExtcel(HttpServletRequest request) throws Exception
	{   
		request.setCharacterEncoding("utf-8");
		List<String[]> ss = new ArrayList<String[]>();
		FileItemFactory factory = new DiskFileItemFactory();
		ServletFileUpload se = new ServletFileUpload(factory);
		try {
			List items=(List) se.parseRequest(request);
			Iterator iter = items.iterator();  
			   while (iter.hasNext()) {  
				  // deleteFolder(getRootPath()+"/flieUpload");
				   FileItem item = (FileItem) iter.next();
				   String flieName=item.getName();
				   String[] xx=flieName.split("\\.");
				   
				   InputStream  in =item.getInputStream();
					 Workbook wb = null;
					   
					   if(xx[xx.length-1].equals("xls"))
					   {
						   
						   wb = new HSSFWorkbook(in);
					   }
					   else if(xx[xx.length-1].equals("xlsx"))
					   {
						   wb=new XSSFWorkbook(in);
					   }
					   Sheet sheet = wb.getSheetAt(0);
					int rows = sheet.getPhysicalNumberOfRows();
					if (rows > 0) {
						sheet.getMargin(Sheet.TopMargin);
						for (int j = 0; j < rows; j++) {
							Row row = sheet.getRow(j);
							if (row != null) {
								int cells = row.getLastCellNum();
								if(cells>=0)
								{
								String[] str = new String[cells];
								for (short k = 0; k < cells; k++) {
									Cell cell = row.getCell(k);
									if (cell != null) {
										switch (cell.getCellType()) {
										case Cell.CELL_TYPE_NUMERIC:
											DecimalFormat df = new DecimalFormat("0");
											str[k] =df.format(cell.getNumericCellValue());
											
											if (HSSFDateUtil.isCellDateFormatted(cell)) {
												Date d = cell.getDateCellValue();
												DateFormat formater = new SimpleDateFormat("yyyy-MM-dd");
												str[k] =formater.format(d);
												}
											
											break;
										case Cell.CELL_TYPE_STRING:
											str[k] = cell.getStringCellValue();
											break;
										case Cell.CELL_TYPE_BOOLEAN:
											str[k] = String.valueOf(cell.getBooleanCellValue());
											break;
										case Cell.CELL_TYPE_FORMULA:
											str[k] = String.valueOf(cell.getCellFormula());
											break;
										default:
											break;
										}
									}
								}
							if(str[0]!=null)
							{
								if(str[0].equals("")==false)
								{
							      ss.add(str);
								}
							}
							}
							}
						}
					
					}
			   }
		} catch (FileUploadException e) {
			e.printStackTrace();
		}
		return ss;
	}
	/**
	* @Title: getDataFromExtcelDoubl
	* @Description: 数字类型精确度更高
	* @author lmwang
	* @param request
	* @return
	* @throws Exception
	* @throws
	*/
	public List<String[]> getDataFromExtcelDoubl(HttpServletRequest request) throws Exception
	{   
		request.setCharacterEncoding("utf-8");
		List<String[]> ss = new ArrayList<String[]>(); 
		FileItemFactory factory = new DiskFileItemFactory();
		ServletFileUpload se = new ServletFileUpload(factory);
		try {
			List items=(List) se.parseRequest(request);
			Iterator iter = items.iterator();  
			   while (iter.hasNext()) {  
				  // deleteFolder(getRootPath()+"/flieUpload");
				   FileItem item = (FileItem) iter.next();
				   String flieName=item.getName();
				   String[] xx=flieName.split("\\.");
				   
				   InputStream  in =item.getInputStream();
					 Workbook wb = null;
					   
					   if(xx[xx.length-1].equals("xls"))
					   {
						   
						   wb = new HSSFWorkbook(in);
					   }
					   else if(xx[xx.length-1].equals("xlsx"))
					   {
						   wb=new XSSFWorkbook(in);
						   
					   }
					   Sheet sheet = wb.getSheetAt(0);
					int rows = sheet.getPhysicalNumberOfRows();
					if (rows > 0) {
						sheet.getMargin(Sheet.TopMargin);
						for (int j = 0; j < rows; j++) {
							Row row = sheet.getRow(j);
							if (row != null) {
								int cells = row.getLastCellNum();
								if(cells>=0)
								{
								String[] str = new String[cells];
								for (short k = 0; k < cells; k++) {
									Cell cell = row.getCell(k);
									if (cell != null) {
										switch (cell.getCellType()) {
										case Cell.CELL_TYPE_NUMERIC:
//											DecimalFormat df = new DecimalFormat("###.00000000");
//											str[k] =df.format(cell.getNumericCellValue());
//											break;
											SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
											if (HSSFDateUtil.isCellDateFormatted(cell)) {
								                Date theDate = cell.getDateCellValue();  
								                str[k]  = sdf.format(theDate);  
								            } else {   
								            	DecimalFormat df = new DecimalFormat("###.00000000");
												str[k] =df.format(cell.getNumericCellValue()); 
								            }  
								            break;  
										case Cell.CELL_TYPE_STRING:
											str[k] = cell.getStringCellValue();
											break;
										case Cell.CELL_TYPE_BOOLEAN:
											str[k] = String.valueOf(cell.getBooleanCellValue());
											break;
										case Cell.CELL_TYPE_FORMULA:
											str[k] = String.valueOf(cell.getCellFormula());
											break;
										default:
											break;
										}
									}
								}
							if(str[0]!=null)
							{
								if(str[0].equals("")==false)
								{
							      ss.add(str);
								}
							}
							}
							}
						}
					
					}
			   }
		} catch (FileUploadException e) {
			e.printStackTrace();
		}
		return ss;
	}
	public void outExcel(List<Map<String,Object>> result, AnyFormatDocProp prop, String fileName, HttpServletResponse response, HttpServletRequest request)
	{
		try
		{
			response.reset();
		String head="";	
		fileName=fileName+".xls";
		if(result.size()>0)
		{
			Map<String,Object> map=result.get(0);
			for(Object o:map.keySet())
			{
				head=head+o.toString()+",";	
			}
			head.substring(0, head.length()-1);
		}else{
			Map<String,Object> map=new LinkedHashMap<String, Object>();
			map.put("暂无数据", " ");map.put("             ", "");map.put("                  ", "");map.put("   ", "");map.put("    ", "");
			for(Object o:map.keySet())
			{
			head=head+o.toString()+",";	
			}
			head.substring(0, head.length()-1);
			result.add(map);
		}
		prop.setContxt_head(head);
		prop.setMode(AnyFormatDocumentConstant.STYLE_MODE);
		prop.setType(AnyFormatDocumentConstant.TYPE_XLS);
		if("结汇信息.xlsx".equals(fileName))
		{
			prop.setContxt_total(head.split(","));
		}
		setDataMap(result);
		setProp(prop);	
		setFileName(fileName);
		response.reset();
		response.setContentType("application/octet-stream;charset=ISO8859-1");
		response.setHeader("Content-Disposition","attachment;filename="+getFileName(request)+"");
		InputStream is=getInputStream();

		OutputStream out = response.getOutputStream();
		byte[] data = new byte[1024];
		int length = -1;
		while((length = is.read(data)) != -1) {
		out.write(data, 0, length);
		out.close();
		is.close();
		}
		}
		catch( Exception e)
		{
		e.printStackTrace();	
		}
	}
	@Override
	public void loadData() {
		// TODO Auto-generated method stub
		
	}
	/**
	* @Title: writeExcelWithMerg
	* @Description: 导出excel并设置格式，合并单元格
	* @author lmwang
	* @param fileName   文件名称
	* @param title		标题	
	* @param dataList		数据源	
	* @param rowsName	列名称
    * @param column	有需要合并项的列
	* @param request
	* @param response
	* @throws Exception
	* @throws
	*/
	public void writeExcelWithMerg(String fileName, String title, List<Object[]> dataList, String[] rowsName, int column[], HttpServletRequest request, HttpServletResponse response) throws Exception{
        try{  
        	response.reset();
        	if(rowsName==null||rowsName.length<1){
        		return;
        	}
        	//每个sheet页条数
        	int num=10000;
        	int titleNum=1;
        	//计算分多少页
        	int tempNUm=(int)(Math.ceil((double)dataList.size()/num));
        	if(tempNUm==0){
        		tempNUm=1;
        	}
        	//SXSSFWorkbook workbook = new SXSSFWorkbook(5000); // 创建工作簿对象  
        	HSSFWorkbook workbook=new HSSFWorkbook();
             Sheet sheet =null;
            //单元格样式对象  
             CellStyle style = this.getStyle(workbook);
          //sheet样式定义【getColumnTopStyle()/getStyle()均为自定义方法 - 在下面  - 可扩展】  
             CellStyle columnTopStyle = this.getColumnTopStyle(workbook);//获取列头样式对象
            //循环sheet页次数
            for(int sheetNum=0;sheetNum<tempNUm;sheetNum++){
            	//每页的起始数据位置
            	int startNum=num*sheetNum;
            	//每页的数据数
            	int endNum=0;
            	if(tempNUm-sheetNum==1){
            		endNum=dataList.size()-sheetNum*num;
            	}else{
            		endNum=num;
            	}
                //标题不为空是设置格式
                if(title!=null&&!"".equals(title)){
                	String a=title+sheetNum;
                	 sheet = workbook.createSheet(a);                  // 创建工作表  
            		titleNum=3;
            		// 产生表格标题行  
                    Row rowm = sheet.createRow(0);
                    Cell cellTiltle = rowm.createCell(0);
                    sheet.addMergedRegion(new CellRangeAddress(0, 1, 0, (rowsName.length-1)));
            		cellTiltle.setCellStyle(columnTopStyle);  
                    cellTiltle.setCellValue(title);  
            	}else{
            		  sheet = workbook.createSheet("sheet"+sheetNum);                  // 创建工作表  
            	}
                  
                // 定义所需列数  
                int columnNum = rowsName.length;  
                Row rowRowName = sheet.createRow(2);                // 在索引2的位置创建行(最顶端的行开始的第二行)
                  
                // 将列头设置到sheet的单元格中  
                for(int n=0;n<columnNum;n++){  
                    Cell cellRowName = rowRowName.createCell(n);               //创建列头对应个数的单元格
                    cellRowName.setCellType(HSSFCell.CELL_TYPE_STRING);             //设置列头单元格的数据类型
                    HSSFRichTextString text = new HSSFRichTextString(rowsName[n]);
                    cellRowName.setCellValue(text);                                 //设置列头单元格的值  
                    cellRowName.setCellStyle(columnTopStyle);  
                    //sheet.autoSizeColumn(n);//设置自适应列宽                     //设置列头单元格样式  
                }  
                  
                //将查询出的数据设置到sheet对应的单元格中  
                for(int i=0;i<endNum;i++){  
                      
                    Object[] obj = dataList.get(i+startNum);//遍历每个对象  
                    Row row = sheet.createRow(i+titleNum);//创建所需的行数
                      
                    for(int j=0; j<obj.length; j++){  
                        Cell cell = null;   //设置单元格的数据类型
                       /* if(j == 0){  
                            cell = row.createCell(j,HSSFCell.CELL_TYPE_NUMERIC);  
                            cell.setCellValue(i+1);   
                        }else{ */ 
                            cell = row.createCell(j, Cell.CELL_TYPE_STRING);
                            if(!"".equals(obj[j]) && obj[j] != null){  
                                cell.setCellValue(obj[j].toString());                       //设置单元格的值  
                            }else{
                            	cell.setCellValue(""); 
                            }  
                       /* }  */
                        cell.setCellStyle(style);                                   //设置单元格样式  
                       // sheet.autoSizeColumn(j);//设置自适应列宽
                    }  
                }  
                //让列宽随着导出的列长自动适应  
                for (int colNum = 0; colNum < columnNum; colNum++) {  
                    int columnWidth = sheet.getColumnWidth(colNum) / 256;  
                    for (int rowNum = 0; rowNum < sheet.getLastRowNum(); rowNum++) {  
                        Row currentRow;
                        //当前行未被使用过  
                        if (sheet.getRow(rowNum) == null) {  
                            currentRow = sheet.createRow(rowNum);  
                        } else {  
                            currentRow = sheet.getRow(rowNum);  
                        }  
                        if (currentRow.getCell(colNum) != null) {  
                            Cell currentCell = currentRow.getCell(colNum);
                            if (currentCell.getCellType() == HSSFCell.CELL_TYPE_STRING) {
                            	try{
                            		int length = currentCell.getStringCellValue().getBytes().length;  
                            		if (columnWidth < length) {  
                            			columnWidth = length;  
                            		}  
                            	}catch(Exception e){
                            		
                            	}
                            }  
                        }  
                    } 
                    try{
                    if(colNum == 0){  
                        sheet.setColumnWidth(colNum, (columnWidth-2) * 256);  
                    }else{  
                        sheet.setColumnWidth(colNum, (columnWidth+4) * 256);  
                    } 
                    }catch(Exception e){
                    	
                    }
                }
                // 单元格合并 
                //需要合并的列              
                if(column!=null){//判断是否需要合并，不需要合并的导出可传null
                	for(int i=0;i<column.length;i++){
                     	for(int j=0;j<endNum;j++){
                     		if(dataList.get(j+startNum)!=null&&!"".equals(dataList.get(j+startNum))){
                     			int n=titleNum;
                     			//查出需要合并的行
                     			for(int k=j+1;k<endNum;k++){
                     				if(dataList.get(k+startNum)!=null&&dataList.get(j+startNum)[column[i]]!=null&&dataList.get(k+startNum)[column[i]]!=null&&dataList.get(j+startNum)[column[i]].equals(dataList.get(k+startNum)[column[i]])){
                     					n++;
                     				}else{
                     					// 四个参数分别是：起始行，结束行，起始列，结束列   
                             			/*sheet.addMergedRegion(new CellRangeAddress(j+titleNum,  j+n,   (short) (column[i]),
                             					(short) (column[i])));
                             			j=j+n-1;*/
                     					break;
                     				}
                     			}
                     			sheet.addMergedRegion(new CellRangeAddress(j+titleNum,  j+n,   (short) (column[i]),
                     					(short) (column[i])));
                     			j=j+n-titleNum;
                     		}
                             
                     	}
                     }
                }
            /*    for(int i=0;i<column.length;i++){
                	for(int j=0;j<endNum;j++){
                		if(dataList.get(j+startNum)!=null&&!"".equals(dataList.get(j+startNum))){
                			
                			int n=titleNum;
                			//查出需要合并的行
                			for(int k=j+1;k<endNum;k++){
                				if(dataList.get(k+startNum)!=null&&dataList.get(j+startNum)[column[i]]!=null&&dataList.get(k+startNum)[column[i]]!=null&&dataList.get(j+startNum)[column[i]].equals(dataList.get(k+startNum)[column[i]])){
                					n++;
                					 Row rowa = sheet.getRow(k+titleNum);
                					 Cell cella =null;
                					 if(rowa!=null){
                						 cella = rowa.getCell(column[i]); 
                					 }
                					 if(null!=cella){
                						 cella.setCellValue(""); 
                					 }
                				}else{
                					// 四个参数分别是：起始行，结束行，起始列，结束列   
                        			sheet.addMergedRegion(new CellRangeAddress(j+titleNum,  j+n,   (short) (column[i]),
                        					(short) (column[i])));
                					break;
                				}
                			}
                			sheet.addMergedRegion(new CellRangeAddress(j+titleNum,  j+n,   (short) (column[i]),
                					(short) (column[i])));
                		}
                        
                	}
                }*/
            }
            
            
           
            if(workbook !=null){  
                try  
                {  
                	setFileName(fileName);
            		
                    String headStr = "attachment; filename=\"" + getFileName(request) + "\"";  
                    response.setContentType("application/octet-stream;charset=ISO8859-1");
                    response.setHeader("Content-Disposition", headStr);  
                    OutputStream out = response.getOutputStream();  
                    workbook.write(out); 
                    out.close();
                }  
                catch (IOException e)  
                {  
                    e.printStackTrace();  
                }  
            }  
  
        }catch(Exception e){  
            e.printStackTrace();  
        }  
          
    }  
	/**
	* @Title: writeExcelData
	* @Description: 导出报关单相关的商户信息、货物信息、采购商信息
	* @author lmwang
	* @param fileName
	* @param dataList
	* @param rowsName
	* @param rowsName2
	* @param rowsName3
	* @param request
	* @param response
	* @throws Exception
	* @throws
	*/
	public void writeExcelData(String fileName, List<Object[]> dataList, String[] rowsName, String[] rowsName2, String[] rowsName3, HttpServletRequest request, HttpServletResponse response) throws Exception{
        try{  
        	if(rowsName==null||rowsName.length<1){
        		return;
        	}
        	int titleNum=1;
            HSSFWorkbook workbook = new HSSFWorkbook();                     // 创建工作簿对象
            HSSFSheet sheet =null;
            HSSFSheet sheet2 =null;
            HSSFSheet sheet3 =null;
            //单元格样式对象  
            HSSFCellStyle style = this.getStyle(workbook);
          //sheet样式定义【getColumnTopStyle()/getStyle()均为自定义方法 - 在下面  - 可扩展】  
            HSSFCellStyle columnTopStyle = this.getColumnTopStyle(workbook);//获取列头样式对象
            //标题不为空是设置格式
        		  sheet = workbook.createSheet("商户信息");                  // 创建工作表  
        		  sheet2 = workbook.createSheet("货物信息"); 
        		  sheet3 = workbook.createSheet("采购商信息"); 
        		  
            // 定义所需列数  
            int columnNum = rowsName.length;  
            int columnNum2 = rowsName2.length;  
            int columnNum3 = rowsName3.length;  
            HSSFRow rowRowName = sheet.createRow(0);                // 在索引2的位置创建行(最顶端的行开始的第二行)
            HSSFRow rowRowName2 = sheet2.createRow(0);
            HSSFRow rowRowName3 = sheet3.createRow(0);
            // 将列头设置到sheet的单元格中  
            for(int n=0;n<columnNum;n++){  
                HSSFCell cellRowName = rowRowName.createCell(n);               //创建列头对应个数的单元格
                cellRowName.setCellType(HSSFCell.CELL_TYPE_STRING);             //设置列头单元格的数据类型
                HSSFRichTextString text = new HSSFRichTextString(rowsName[n]);
                cellRowName.setCellValue(text);                                 //设置列头单元格的值  
                cellRowName.setCellStyle(columnTopStyle);                       //设置列头单元格样式  
            }  
            for(int n=0;n<columnNum2;n++){  
                HSSFCell cellRowName2 = rowRowName2.createCell(n);               //创建列头对应个数的单元格
                cellRowName2.setCellType(HSSFCell.CELL_TYPE_STRING);             //设置列头单元格的数据类型
                HSSFRichTextString text = new HSSFRichTextString(rowsName2[n]);
                cellRowName2.setCellValue(text);                                 //设置列头单元格的值  
                cellRowName2.setCellStyle(columnTopStyle);                       //设置列头单元格样式  
            }  
            for(int n=0;n<columnNum3;n++){  
                HSSFCell cellRowName3 = rowRowName3.createCell(n);               //创建列头对应个数的单元格
                cellRowName3.setCellType(HSSFCell.CELL_TYPE_STRING);             //设置列头单元格的数据类型
                HSSFRichTextString text = new HSSFRichTextString(rowsName3[n]);
                cellRowName3.setCellValue(text);                                 //设置列头单元格的值  
                cellRowName3.setCellStyle(columnTopStyle);                       //设置列头单元格样式  
            }  
            //将查询出的数据设置到sheet对应的单元格中  
            for(int i=0;i<dataList.size();i++){  
                  
                Object[] obj = dataList.get(i);//遍历每个对象  
                HSSFRow row = sheet.createRow(i+titleNum);//创建所需的行数
                  
                for(int j=0; j<8; j++){  
                    HSSFCell cell = null;   //设置单元格的数据类型
                   /* if(j == 0){  
                        cell = row.createCell(j,HSSFCell.CELL_TYPE_NUMERIC);  
                        cell.setCellValue(i+1);   
                    }else{ */ 
                        cell = row.createCell(j, HSSFCell.CELL_TYPE_STRING);
                        if(!"".equals(obj[j]) && obj[j] != null){  
                            cell.setCellValue(obj[j].toString());                       //设置单元格的值  
                        }else{
                        	cell.setCellValue(""); 
                        }  
                   /* }  */
                    cell.setCellStyle(style);                                   //设置单元格样式  
                }  
            }  
            for(int i=0;i<dataList.size();i++){  
                
                Object[] obj = dataList.get(i);//遍历每个对象  
                HSSFRow row = sheet2.createRow(i+titleNum);//创建所需的行数
                  
                for(int j=8; j<18; j++){  
                    HSSFCell cell = null;   //设置单元格的数据类型
                   /* if(j == 0){  
                        cell = row.createCell(j,HSSFCell.CELL_TYPE_NUMERIC);  
                        cell.setCellValue(i+1);   
                    }else{ */ 
                        cell = row.createCell(j-8, HSSFCell.CELL_TYPE_STRING);
                        if(!"".equals(obj[j]) && obj[j] != null){  
                            cell.setCellValue(obj[j].toString());                       //设置单元格的值  
                        }else{
                        	cell.setCellValue(""); 
                        }  
                   /* }  */
                    cell.setCellStyle(style);                                   //设置单元格样式  
                }  
            }  
            for(int i=0;i<dataList.size();i++){  
                
                Object[] obj = dataList.get(i);//遍历每个对象  
                HSSFRow row = sheet3.createRow(i+titleNum);//创建所需的行数
                  
                for(int j=18; j<22; j++){  
                    HSSFCell cell = null;   //设置单元格的数据类型
                   /* if(j == 0){  
                        cell = row.createCell(j,HSSFCell.CELL_TYPE_NUMERIC);  
                        cell.setCellValue(i+1);   
                    }else{ */ 
                        cell = row.createCell(j-18, HSSFCell.CELL_TYPE_STRING);
                        if(!"".equals(obj[j]) && obj[j] != null){  
                            cell.setCellValue(obj[j].toString());                       //设置单元格的值  
                        }else{
                        	cell.setCellValue(""); 
                        }  
                   /* }  */
                    cell.setCellStyle(style);                                   //设置单元格样式  
                }  
            }  
            //让列宽随着导出的列长自动适应  
            for (int colNum = 0; colNum < columnNum; colNum++) {  
                int columnWidth = sheet.getColumnWidth(colNum) / 256;  
                for (int rowNum = 0; rowNum < sheet.getLastRowNum(); rowNum++) {  
                    HSSFRow currentRow;
                    //当前行未被使用过  
                    if (sheet.getRow(rowNum) == null) {  
                        currentRow = sheet.createRow(rowNum);  
                    } else {  
                        currentRow = sheet.getRow(rowNum);  
                    }  
                    if (currentRow.getCell(colNum) != null) {  
                        HSSFCell currentCell = currentRow.getCell(colNum);
                        if (currentCell.getCellType() == HSSFCell.CELL_TYPE_STRING) {
                        	try{
                        		int length = currentCell.getStringCellValue().getBytes().length;  
                        		if (columnWidth < length) {  
                        			columnWidth = length;  
                        		}  
                        	}catch(Exception e){
                        		
                        	}
                        }  
                    }  
                }  
                if(colNum == 0){  
                    sheet.setColumnWidth(colNum, (columnWidth-2) * 256);  
                }else{  
                    sheet.setColumnWidth(colNum, (columnWidth+4) * 256);  
                }  
            }
            for (int colNum = 0; colNum < columnNum2; colNum++) {  
                int columnWidth = sheet2.getColumnWidth(colNum) / 256;  
                for (int rowNum = 0; rowNum < sheet2.getLastRowNum(); rowNum++) {  
                    HSSFRow currentRow;
                    //当前行未被使用过  
                    if (sheet2.getRow(rowNum) == null) {  
                        currentRow = sheet2.createRow(rowNum);  
                    } else {  
                        currentRow = sheet2.getRow(rowNum);  
                    }  
                    if (currentRow.getCell(colNum) != null) {  
                        HSSFCell currentCell = currentRow.getCell(colNum);
                        if (currentCell.getCellType() == HSSFCell.CELL_TYPE_STRING) {
                        	try{
                        		int length = currentCell.getStringCellValue().getBytes().length;  
                        		if (columnWidth < length) {  
                        			columnWidth = length;  
                        		}  
                        	}catch(Exception e){
                        		
                        	}
                        }  
                    }  
                }  
                if(colNum == 0){  
                    sheet2.setColumnWidth(colNum, (columnWidth-2) * 256);  
                }else{  
                    sheet2.setColumnWidth(colNum, (columnWidth+4) * 256);  
                }  
            }
            for (int colNum = 0; colNum < columnNum3; colNum++) {  
                int columnWidth = sheet3.getColumnWidth(colNum) / 256;  
                for (int rowNum = 0; rowNum < sheet3.getLastRowNum(); rowNum++) {  
                    HSSFRow currentRow;
                    //当前行未被使用过  
                    if (sheet3.getRow(rowNum) == null) {  
                        currentRow = sheet3.createRow(rowNum);  
                    } else {  
                        currentRow = sheet3.getRow(rowNum);  
                    }  
                    if (currentRow.getCell(colNum) != null) {  
                        HSSFCell currentCell = currentRow.getCell(colNum);
                        if (currentCell.getCellType() == HSSFCell.CELL_TYPE_STRING) {
                        	try{
                        		int length = currentCell.getStringCellValue().getBytes().length;  
                        		if (columnWidth < length) {  
                        			columnWidth = length;  
                        		}  
                        	}catch(Exception e){
                        		
                        	}
                        }  
                    }  
                }  
                if(colNum == 0){  
                    sheet3.setColumnWidth(colNum, (columnWidth-2) * 256);  
                }else{  
                    sheet3.setColumnWidth(colNum, (columnWidth+4) * 256);  
                }  
            }
          
            if(workbook !=null){  
                try  
                {  
                	setFileName(fileName);
            		response.reset();
                    String headStr = "attachment; filename=\"" + getFileName(request) + "\"";  
                    response.setContentType("application/octet-stream;charset=ISO8859-1");
                    response.setHeader("Content-Disposition", headStr);  
                    OutputStream out = response.getOutputStream();  
                    workbook.write(out);  
                }  
                catch (IOException e)  
                {  
                    e.printStackTrace();  
                }  
            }  
  
        }catch(Exception e){  
            e.printStackTrace();  
        }  
          
    }  
	/*  
     * 列头单元格样式 
     */      
    public HSSFCellStyle getColumnTopStyle(HSSFWorkbook workbook) {
          
          // 设置字体  
          HSSFFont font = workbook.createFont();
          //设置字体大小  
          font.setFontHeightInPoints((short)11);  
          //字体加粗  
          font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
          //设置字体名字   
          font.setFontName("Courier New");  
          //设置样式;   
          HSSFCellStyle style = workbook.createCellStyle();
          //设置底边框;   
          style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
          //设置底边框颜色;    
          style.setBottomBorderColor(HSSFColor.BLACK.index);
          //设置左边框;     
          style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
          //设置左边框颜色;   
          style.setLeftBorderColor(HSSFColor.BLACK.index);
          //设置右边框;   
          style.setBorderRight(HSSFCellStyle.BORDER_THIN);
          //设置右边框颜色;   
          style.setRightBorderColor(HSSFColor.BLACK.index);
          //设置顶边框;   
          style.setBorderTop(HSSFCellStyle.BORDER_THIN);
          //设置顶边框颜色;    
          style.setTopBorderColor(HSSFColor.BLACK.index);
          //在样式用应用设置的字体;    
          style.setFont(font);  
          //设置自动换行;   
          style.setWrapText(false);  
          //设置水平对齐的样式为居中对齐;    
          style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
          //设置垂直对齐的样式为居中对齐;   
          style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
            
          return style;  
            
    }  
      
    /*   
     * 列数据信息单元格样式 
     */    
    public HSSFCellStyle getStyle(HSSFWorkbook workbook) {
          // 设置字体  
          HSSFFont font = workbook.createFont();
          //设置字体大小  
          //font.setFontHeightInPoints((short)10);  
          //字体加粗  
          //font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);  
          //设置字体名字   
          font.setFontName("Courier New");  
          //设置样式;   
          HSSFCellStyle style = workbook.createCellStyle();
          //设置底边框;   
          style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
          //设置底边框颜色;    
          style.setBottomBorderColor(HSSFColor.BLACK.index);
          //设置左边框;     
          style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
          //设置左边框颜色;   
          style.setLeftBorderColor(HSSFColor.BLACK.index);
          //设置右边框;   
          style.setBorderRight(HSSFCellStyle.BORDER_THIN);
          //设置右边框颜色;   
          style.setRightBorderColor(HSSFColor.BLACK.index);
          //设置顶边框;   
          style.setBorderTop(HSSFCellStyle.BORDER_THIN);
          //设置顶边框颜色;    
          style.setTopBorderColor(HSSFColor.BLACK.index);
          //在样式用应用设置的字体;    
          style.setFont(font);  
          //设置自动换行;   
          style.setWrapText(false);  
          //设置水平对齐的样式为居中对齐;    
          style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
          //设置垂直对齐的样式为居中对齐;   
          style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
           
          return style;  
      
    }  
    
    /**
     * 
     * @param fileName
     * @param title
     * @param dataList
     * @param rowsName
     * @param request
     * @param response
     * @throws Exception
     * @Description: 收汇查询里面的导出  导出的excel里面最下方要有合计
     * @author zhangyc
     * @date 2017-1-10 上午10:51:26
     */
    public void writeSHExcel(String fileName, String title, List<Object[]> dataList, String[] rowsName, HttpServletRequest request, HttpServletResponse response) throws Exception{
        try{  
        	if(rowsName==null||rowsName.length<1){
        		return;
        	}
        	int titleNum=1;
            HSSFWorkbook workbook = new HSSFWorkbook();                     // 创建工作簿对象
            HSSFSheet sheet =null;
            //单元格样式对象  
            HSSFCellStyle style = this.getStyle(workbook);
          //sheet样式定义【getColumnTopStyle()/getStyle()均为自定义方法 - 在下面  - 可扩展】  
            HSSFCellStyle columnTopStyle = this.getColumnTopStyle(workbook);//获取列头样式对象
            //标题不为空是设置格式
            if(title!=null&&!"".equals(title)){
            	 sheet = workbook.createSheet(title);                  // 创建工作表  
        		titleNum=3;
        		// 产生表格标题行  
                HSSFRow rowm = sheet.createRow(0);
                HSSFCell cellTiltle = rowm.createCell(0);
                sheet.addMergedRegion(new CellRangeAddress(0, 1, 0, (rowsName.length-1)));
        		cellTiltle.setCellStyle(columnTopStyle);  
                cellTiltle.setCellValue(title);  
        	}else{
        		  sheet = workbook.createSheet("sheet");                  // 创建工作表  
        	}
              
            // 定义所需列数  
            int columnNum = rowsName.length;  
            HSSFRow rowRowName = sheet.createRow(2);                // 在索引2的位置创建行(最顶端的行开始的第二行)
              
            // 将列头设置到sheet的单元格中  
            for(int n=0;n<columnNum;n++){  
                HSSFCell cellRowName = rowRowName.createCell(n);               //创建列头对应个数的单元格
                cellRowName.setCellType(HSSFCell.CELL_TYPE_STRING);             //设置列头单元格的数据类型
                HSSFRichTextString text = new HSSFRichTextString(rowsName[n]);
                cellRowName.setCellValue(text);                                 //设置列头单元格的值  
                cellRowName.setCellStyle(columnTopStyle);                       //设置列头单元格样式  
            }  
            
            double settleTotal = 0;
			double restTotal = 0;
			for(int i=0; i<dataList.size(); i++)
			{   
				 Object[] obj1 = dataList.get(i);
				settleTotal = settleTotal +  Double.parseDouble(obj1[7].toString().split("\'")[1]);
				restTotal = restTotal + Double.parseDouble(obj1[8].toString().split("\'")[1]);
			}
			
            //将查询出的数据设置到sheet对应的单元格中  
            for(int i=0;i<(dataList.size()+1);i++){  
               HSSFRow row = sheet.createRow(i+titleNum);//创建所需的行数
               HSSFCell cell = null;   //设置单元格的数据类型
               if(i == dataList.size()){
            	   cell = row.createCell(0, HSSFCell.CELL_TYPE_STRING);
            	   cell.setCellValue("合计单数(单):");
            	   cell = row.createCell(1, HSSFCell.CELL_TYPE_STRING);
            	   cell.setCellValue(dataList.size());
            	   cell = row.createCell(4, HSSFCell.CELL_TYPE_STRING);
            	   cell.setCellValue("收汇总金额折美元:");
            	   cell = row.createCell(5, HSSFCell.CELL_TYPE_STRING);
            	   cell.setCellValue(settleTotal);
            	   cell = row.createCell(6, HSSFCell.CELL_TYPE_STRING);
            	   cell.setCellValue("收汇总金额折人民币:");
            	   cell = row.createCell(7, HSSFCell.CELL_TYPE_STRING);
            	   cell.setCellValue(restTotal);
               }else{
            	   Object[] obj = dataList.get(i);//遍历每个对象  
                   for(int j=0; j<obj.length; j++){  
                       cell = row.createCell(j, HSSFCell.CELL_TYPE_STRING);
                       if(!"".equals(obj[j]) && obj[j] != null){  
                           cell.setCellValue(obj[j].toString());                       //设置单元格的值  
                       }else{
                       	cell.setCellValue(""); 
                       }  
                       cell.setCellStyle(style);                                   //设置单元格样式  
                   }   
               }   
            }  
            //让列宽随着导出的列长自动适应  
            for (int colNum = 0; colNum < columnNum; colNum++) {  
                int columnWidth = sheet.getColumnWidth(colNum) / 256;  
                for (int rowNum = 0; rowNum < sheet.getLastRowNum(); rowNum++) {  
                    HSSFRow currentRow;
                    //当前行未被使用过  
                    if (sheet.getRow(rowNum) == null) {  
                        currentRow = sheet.createRow(rowNum);  
                    } else {  
                        currentRow = sheet.getRow(rowNum);  
                    }  
                    if (currentRow.getCell(colNum) != null) {  
                        HSSFCell currentCell = currentRow.getCell(colNum);
                        if (currentCell.getCellType() == HSSFCell.CELL_TYPE_STRING) {
                        	try{
                        		int length = currentCell.getStringCellValue().getBytes().length;  
                        		if (columnWidth < length) {  
                        			columnWidth = length;  
                        		}  
                        	}catch(Exception e){
                        		
                        	}
                        }  
                    }  
                }  
                if(colNum == 0){  
                    sheet.setColumnWidth(colNum, (columnWidth-2) * 256);  
                }else{  
                    sheet.setColumnWidth(colNum, (columnWidth+4) * 256);  
                }  
            }

            if(workbook !=null){  
                try  
                {  
                	setFileName(fileName);
            		response.reset();
                    String headStr = "attachment; filename=\"" + getFileName(request) + "\"";  
                    response.setContentType("application/octet-stream;charset=ISO8859-1");
                    response.setHeader("Content-Disposition", headStr);  
                    OutputStream out = response.getOutputStream();  
                    workbook.write(out);  
                }  
                catch (IOException e)  
                {  
                    e.printStackTrace();  
                }  
            }  
  
        }catch(Exception e){  
            e.printStackTrace();  
        }  
          
    }  
 
    /*   
     * 列数据信息单元格样式 
     */    
    public CellStyle getStyle(SXSSFWorkbook workbook) {
          // 设置字体  
    	 Font font = workbook.createFont();
          //设置字体大小  
          //font.setFontHeightInPoints((short)10);  
          //字体加粗  
          //font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);  
          //设置字体名字   
          font.setFontName("Courier New");  
          //设置样式;   
           CellStyle style = workbook.createCellStyle();
          //设置底边框;   
          style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
          //设置底边框颜色;    
          style.setBottomBorderColor(HSSFColor.BLACK.index);
          //设置左边框;     
          style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
          //设置左边框颜色;   
          style.setLeftBorderColor(HSSFColor.BLACK.index);
          //设置右边框;   
          style.setBorderRight(HSSFCellStyle.BORDER_THIN);
          //设置右边框颜色;   
          style.setRightBorderColor(HSSFColor.BLACK.index);
          //设置顶边框;   
          style.setBorderTop(HSSFCellStyle.BORDER_THIN);
          //设置顶边框颜色;    
          style.setTopBorderColor(HSSFColor.BLACK.index);
          //在样式用应用设置的字体;    
          style.setFont(font);  
          //设置自动换行;   
          style.setWrapText(false);  
          //设置水平对齐的样式为居中对齐;    
          style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
          //设置垂直对齐的样式为居中对齐;   
          style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
           
          return style;  
      
    }  
    public CellStyle getColumnTopStyle(SXSSFWorkbook workbook) {
        
        // 设置字体  
         Font font = workbook.createFont();
        //设置字体大小  
        font.setFontHeightInPoints((short)11);  
        //字体加粗  
        font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
        //设置字体名字   
        font.setFontName("Courier New");  
        //设置样式;   
         CellStyle style = workbook.createCellStyle();
        //设置底边框;   
        style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
        //设置底边框颜色;    
        style.setBottomBorderColor(HSSFColor.BLACK.index);
        //设置左边框;     
        style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
        //设置左边框颜色;   
        style.setLeftBorderColor(HSSFColor.BLACK.index);
        //设置右边框;   
        style.setBorderRight(HSSFCellStyle.BORDER_THIN);
        //设置右边框颜色;   
        style.setRightBorderColor(HSSFColor.BLACK.index);
        //设置顶边框;   
        style.setBorderTop(HSSFCellStyle.BORDER_THIN);
        //设置顶边框颜色;    
        style.setTopBorderColor(HSSFColor.BLACK.index);
        //在样式用应用设置的字体;    
        style.setFont(font);  
        //设置自动换行;   
        style.setWrapText(false);  
        //设置水平对齐的样式为居中对齐;    
        style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        //设置垂直对齐的样式为居中对齐;   
        style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
          
        return style;  
          
  }  
 
    /**
     * @param request
     * @return
     * @throws Exception
     * @Description: TODO 读取excel文件
     * @author makai
     * @date 2017-8-7 上午10:06:10
     */
	public List<String[]> getDataFromExtcelN(HttpServletRequest request) throws Exception
	{   
		request.setCharacterEncoding("utf-8");
		List<String[]> ss = new ArrayList<String[]>();
		FileItemFactory factory = new DiskFileItemFactory();
		ServletFileUpload se = new ServletFileUpload(factory);
		try {
			List items=(List) se.parseRequest(request);
			Iterator iter = items.iterator();  
			   while (iter.hasNext()) {  
				  // deleteFolder(getRootPath()+"/flieUpload");
				   FileItem item = (FileItem) iter.next();
				   String flieName=item.getName();
				   String[] xx=flieName.split("\\.");
				   
				   InputStream  in =item.getInputStream();
					 Workbook wb = null;
					   
					   if(xx[xx.length-1].equals("xls"))
					   {
						   
						   wb = new HSSFWorkbook(in);
					   }
					   else if(xx[xx.length-1].equals("xlsx"))
					   {
						   wb=new XSSFWorkbook(in);
						   
					   }
					   Sheet sheet = wb.getSheetAt(0);
					int rows = sheet.getPhysicalNumberOfRows();
					if (rows > 0) {
						sheet.getMargin(Sheet.TopMargin);
						for (int j = 0; j < rows; j++) {
							Row row = sheet.getRow(j);
							if (row != null) {
								int cells = row.getLastCellNum();
								if(cells>=0)
								{
								String[] str = new String[cells];
								for (short k = 0; k < cells; k++) {
									Cell cell = row.getCell(k);
									if (cell != null) {
										switch (cell.getCellType()) {
										case Cell.CELL_TYPE_NUMERIC:
											DecimalFormat df = new DecimalFormat("#########0.00");
											str[k] =df.format(cell.getNumericCellValue());
											
											if (HSSFDateUtil.isCellDateFormatted(cell)) {
												Date d = cell.getDateCellValue();
												DateFormat formater = new SimpleDateFormat("yyyy-MM-dd");
												str[k] =formater.format(d);
												}
											
											break;
										case Cell.CELL_TYPE_STRING:
											str[k] = cell.getStringCellValue();
											break;
										case Cell.CELL_TYPE_BOOLEAN:
											str[k] = String.valueOf(cell.getBooleanCellValue());
											break;
										case Cell.CELL_TYPE_FORMULA:
											str[k] = String.valueOf(cell.getCellFormula());
											break;
										default:
											break;
										}
									}
								}
							if(str[0]!=null)
							{
								if(str[0].equals("")==false)
								{
							      ss.add(str);
								}
							}
							}
							}
						}
					
					}
			   }
		} catch (FileUploadException e) {
			e.printStackTrace();
		}
		return ss;
	}
}
